package cn.com.zsc.income;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import javax.servlet.ServletException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.zsc.income.vo.EmployeeSalaryVO;
import cn.com.zsc.util.ConstValue;

import com.jarp.db.DAO;

/**
 * 员工薪资管理
 * @author 杨景
 *
 */
public class EmpSalaryManage {
	/**
	 * 获取指定月份工资列表
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getEmpSalary(String jso) {
		String resultStr = ConstValue.EMPTY_RECORD_NOT_PAGEING;
		
		DAO dao = new DAO();
		
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String guideID = jsobject.getString("guideID");
		String guideName = jsobject.getString("guideName");
		String salaryDate = jsobject.getString("salaryDate");
		
		String salaryInfoSql = "SELECT B.CL_NAME AS SHOW_DEPT, A.*  FROM IC_WAGE A LEFT JOIN CM_COMMLIST B ON A.DEPT=B.CL_CODE where 1=1";

		if (!"".equals(guideID)) {
			salaryInfoSql += " and A.GUIDEID like '%" + guideID + "%'";
		}
		
		if (!"".equals(guideName)) {
			salaryInfoSql += " and A.GUIDENAME like '%" + guideName + "%'";
		}
		
		if (!"".equals(salaryDate)) {
			salaryInfoSql += " and left(A.DATE,7) ='" + salaryDate + "'";
		}
		
		salaryInfoSql += " group by A.DEPT,A.GUIDENAME";
		
		String subTotalSql = "SELECT DEPT, SUM(BASIC) AS SUB_BASIC,SUM(POST) AS SUB_POST,SUM(WORKAGE) AS SUB_WORKAGE,"
				+ "SUM(EFFICIENT) AS SUB_EFFICIENT,SUM(TRAFFIC) AS SUB_TRAFFIC,SUM(LUNCH) AS SUB_LUNCH,"
				+ "SUM(CAR) AS SUB_CAR,SUM(INSURANCE) AS SUB_INSURANCE,SUM(ALLOWANCE)  AS SUB_ALLOWANCE,"
				+ "SUM(OTHER) AS SUB_OTHER,SUM(STOTAL) AS SUB_STOTAL,SUM(TAX) AS SUB_TAX,SUM(DINSURANCE) AS SUB_DINSURANCE,"
				+ "SUM(DSICK) AS SUB_DSICK,SUM(DLEFT) AS SUB_DLEFT,SUM(DTOTAL) AS SUB_DTOTAL,SUM(ATOTAL) AS SUB_ATOTAL "
				+ "FROM IC_WAGE GROUP BY DEPT;";
		
		String sumTotalSql = "SELECT SUM(BASIC) AS SUM_BASIC,SUM(POST) AS SUM_POST,SUM(WORKAGE) AS SUM_WORKAGE,SUM(EFFICIENT) AS SUM_EFFICIENT,"
				+ "SUM(TRAFFIC) AS SUM_TRAFFIC,SUM(LUNCH) AS SUM_LUNCH,SUM(CAR) AS SUM_CAR,SUM(INSURANCE) AS SUM_INSURANCE,SUM(ALLOWANCE) AS SUM_ALLOWANCE,"
				+ "SUM(OTHER) AS SUM_OTHER,SUM(STOTAL) AS SUM_STOTAL,SUM(TAX) AS SUM_TAX,SUM(DINSURANCE) AS SUM_DINSURANCE,SUM(DSICK) AS SUM_DSICK,"
				+ "SUM(DLEFT) AS SUM_DLEFT,SUM(DTOTAL) AS SUM_DTOTAL,SUM(ATOTAL) AS SUM_ATOTAL FROM IC_WAGE;";
		
		try {
			Vector<Hashtable<String, String>> subTotalVec = dao.getDataSet(ConstValue.DBNAME, subTotalSql);
			
			Vector<Hashtable<String, String>> sumTotalVec = dao.getDataSet(ConstValue.DBNAME, sumTotalSql);
			
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, salaryInfoSql);

			if (v != null && v.size() > 0) {
				List<EmployeeSalaryVO> salaryList = new ArrayList<EmployeeSalaryVO>();
				
				Iterator iterator = v.iterator();
				
				for (int i=0; iterator.hasNext(); i++) {
					Hashtable<String, String> table = (Hashtable<String, String>) iterator.next();
					EmployeeSalaryVO salary = new EmployeeSalaryVO();
					
					salary.setSERIALNUMBER(String.valueOf(i + 1));
					salary.setID(table.get("ID"));
					salary.setDATE(table.get("DATE"));
					salary.setGUIDEID(table.get("GUIDEID"));
					salary.setGUIDENAME(table.get("GUIDENAME"));
					salary.setDEPT(table.get("DEPT"));
					salary.setSHOW_DEPT(table.get("SHOW_DEPT"));
					salary.setBANKNO(table.get("BANKNO"));
					salary.setBASIC(table.get("BASIC"));
					salary.setPOST(table.get("POST"));
					salary.setWORKAGE(table.get("WORKAGE"));
					salary.setEFFICIENT(table.get("EFFICIENT"));
					salary.setTRAFFIC(table.get("TRAFFIC"));
					salary.setLUNCH(table.get("LUNCH"));
					salary.setCAR(table.get("CAR"));
					salary.setINSURANCE(table.get("INSURANCE"));
					salary.setALLOWANCE(table.get("ALLOWANCE"));
					salary.setOTHER(table.get("OTHER"));
					salary.setSTOTAL(table.get("STOTAL"));
					salary.setTAX(table.get("TAX"));
					salary.setDINSURANCE(table.get("DINSURANCE"));
					salary.setDSICK(table.get("DSICK"));
					salary.setDLEFT(table.get("DLEFT"));
					salary.setDTOTAL(table.get("DTOTAL"));
					salary.setATOTAL(table.get("ATOTAL"));
					salary.setREMARKED(table.get("REMARKED"));
					
					salaryList.add(salary);
					
					if (iterator.hasNext()) {
						if (!v.get(i + 1).get("DEPT").equals(salary.getDEPT())) {
							if (subTotalVec != null && subTotalVec.size() > 0) {
								for (int j=0; j<subTotalVec.size(); j++) {
									if (salary.getDEPT().equals(subTotalVec.get(j).get("DEPT"))) {
										EmployeeSalaryVO subTotal = new EmployeeSalaryVO();
										
										subTotal.setSERIALNUMBER("合计");
										subTotal.setBASIC(subTotalVec.get(j).get("SUB_BASIC"));
										subTotal.setPOST(subTotalVec.get(j).get("SUB_POST"));
										subTotal.setWORKAGE(subTotalVec.get(j).get("SUB_WORKAGE"));
										subTotal.setEFFICIENT(subTotalVec.get(j).get("SUB_EFFICIENT"));
										subTotal.setTRAFFIC(subTotalVec.get(j).get("SUB_TRAFFIC"));
										subTotal.setLUNCH(subTotalVec.get(j).get("SUB_LUNCH"));
										subTotal.setCAR(subTotalVec.get(j).get("SUB_CAR"));
										subTotal.setINSURANCE(subTotalVec.get(j).get("SUB_INSURANCE"));
										subTotal.setALLOWANCE(subTotalVec.get(j).get("SUB_ALLOWANCE"));
										subTotal.setOTHER(subTotalVec.get(j).get("SUB_OTHER"));
										subTotal.setSTOTAL(subTotalVec.get(j).get("SUB_STOTAL"));
										subTotal.setTAX(subTotalVec.get(j).get("SUB_TAX"));
										subTotal.setDINSURANCE(subTotalVec.get(j).get("SUB_DINSURANCE"));
										subTotal.setDSICK(subTotalVec.get(j).get("SUB_DSICK"));
										subTotal.setDLEFT(subTotalVec.get(j).get("SUB_DLEFT"));
										subTotal.setDTOTAL(subTotalVec.get(j).get("SUB_DTOTAL"));
										subTotal.setATOTAL(subTotalVec.get(j).get("SUB_ATOTAL"));
										subTotal.setREMARKED(subTotalVec.get(j).get("SUB_REMARKED"));
										
										salaryList.add(subTotal);
									}
								}
							}
						}
					} else {
						if (subTotalVec != null && subTotalVec.size() > 0) {
							for (int j=0; j<subTotalVec.size(); j++) {
								if (salary.getDEPT().equals(subTotalVec.get(j).get("DEPT"))) {
									EmployeeSalaryVO subTotal = new EmployeeSalaryVO();
									
									subTotal.setSERIALNUMBER("合计");
									subTotal.setBASIC(subTotalVec.get(j).get("SUB_BASIC"));
									subTotal.setPOST(subTotalVec.get(j).get("SUB_POST"));
									subTotal.setWORKAGE(subTotalVec.get(j).get("SUB_WORKAGE"));
									subTotal.setEFFICIENT(subTotalVec.get(j).get("SUB_EFFICIENT"));
									subTotal.setTRAFFIC(subTotalVec.get(j).get("SUB_TRAFFIC"));
									subTotal.setLUNCH(subTotalVec.get(j).get("SUB_LUNCH"));
									subTotal.setCAR(subTotalVec.get(j).get("SUB_CAR"));
									subTotal.setINSURANCE(subTotalVec.get(j).get("SUB_INSURANCE"));
									subTotal.setALLOWANCE(subTotalVec.get(j).get("SUB_ALLOWANCE"));
									subTotal.setOTHER(subTotalVec.get(j).get("SUB_OTHER"));
									subTotal.setSTOTAL(subTotalVec.get(j).get("SUB_STOTAL"));
									subTotal.setTAX(subTotalVec.get(j).get("SUB_TAX"));
									subTotal.setDINSURANCE(subTotalVec.get(j).get("SUB_DINSURANCE"));
									subTotal.setDSICK(subTotalVec.get(j).get("SUB_DSICK"));
									subTotal.setDLEFT(subTotalVec.get(j).get("SUB_DLEFT"));
									subTotal.setDTOTAL(subTotalVec.get(j).get("SUB_DTOTAL"));
									subTotal.setATOTAL(subTotalVec.get(j).get("SUB_ATOTAL"));
									subTotal.setREMARKED(subTotalVec.get(j).get("SUB_REMARKED"));
									
									salaryList.add(subTotal);
								}
							}
						}
						
						if (sumTotalVec != null && sumTotalVec.size() > 0) {
							for (int k=0; k<sumTotalVec.size(); k++) {
								EmployeeSalaryVO sumTotal = new EmployeeSalaryVO();
								
								sumTotal.setSERIALNUMBER("总计");
								sumTotal.setBASIC(sumTotalVec.get(k).get("SUM_BASIC"));
								sumTotal.setPOST(sumTotalVec.get(k).get("SUM_POST"));
								sumTotal.setWORKAGE(sumTotalVec.get(k).get("SUM_WORKAGE"));
								sumTotal.setEFFICIENT(sumTotalVec.get(k).get("SUM_EFFICIENT"));
								sumTotal.setTRAFFIC(sumTotalVec.get(k).get("SUM_TRAFFIC"));
								sumTotal.setLUNCH(sumTotalVec.get(k).get("SUM_LUNCH"));
								sumTotal.setCAR(sumTotalVec.get(k).get("SUM_CAR"));
								sumTotal.setINSURANCE(sumTotalVec.get(k).get("SUM_INSURANCE"));
								sumTotal.setALLOWANCE(sumTotalVec.get(k).get("SUM_ALLOWANCE"));
								sumTotal.setOTHER(sumTotalVec.get(k).get("SUM_OTHER"));
								sumTotal.setSTOTAL(sumTotalVec.get(k).get("SUM_STOTAL"));
								sumTotal.setTAX(sumTotalVec.get(k).get("SUM_TAX"));
								sumTotal.setDINSURANCE(sumTotalVec.get(k).get("SUM_DINSURANCE"));
								sumTotal.setDSICK(sumTotalVec.get(k).get("SUM_DSICK"));
								sumTotal.setDLEFT(sumTotalVec.get(k).get("SUM_DLEFT"));
								sumTotal.setDTOTAL(sumTotalVec.get(k).get("SUM_DTOTAL"));
								sumTotal.setATOTAL(sumTotalVec.get(k).get("SUM_ATOTAL"));
								sumTotal.setREMARKED(sumTotalVec.get(k).get("SUM_REMARKED"));
								
								salaryList.add(sumTotal);
							}
						}
					}
				}
				
				JSONArray json = JSONArray.fromObject(salaryList);
				
				resultStr = json.toString();
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
		
		return resultStr;
	}
	
	/**
	 * 修改员工薪资
	 * @param jso
	 * @return
	 */
	public String updateEmpSalary(String jso) {
		JSONArray array = JSONArray.fromObject(jso);

		DAO dao = new DAO();
		ArrayList<String> sqlList = new ArrayList<String>();
		
		String sql = "";
		
		for (int i=0; i<array.length(); i++) {
			JSONObject jsobject = array.getJSONObject(i);
			
			Integer id = "".equals(jsobject.getString("id"))?null:Integer.parseInt(jsobject.getString("id"));
			String date = jsobject.getString("date");
			String guideID = jsobject.getString("guideID");
			String guideName = jsobject.getString("guideName");
			Integer dept = "".equals(jsobject.getString("dept"))?null:Integer.parseInt(jsobject.getString("dept"));
			String bankno = jsobject.getString("bankno");
			Double basic = "".equals(jsobject.getString("basic"))?null:Double.parseDouble(jsobject.getString("basic"));
			Double post = "".equals(jsobject.getString("post"))?null:Double.parseDouble(jsobject.getString("post"));
			Double workage = "".equals(jsobject.getString("workage"))?null:Double.parseDouble(jsobject.getString("workage"));
			Double efficient = "".equals(jsobject.getString("efficient"))?null:Double.parseDouble(jsobject.getString("efficient"));
			Double traffic = "".equals(jsobject.getString("traffic"))?null:Double.parseDouble(jsobject.getString("traffic"));
			Double lunch = "".equals(jsobject.getString("lunch"))?null:Double.parseDouble(jsobject.getString("lunch"));
			Double car = "".equals(jsobject.getString("car"))?null:Double.parseDouble(jsobject.getString("car"));
			Double insurance = "".equals(jsobject.getString("insurance"))?null:Double.parseDouble(jsobject.getString("insurance"));
			Double allowance = "".equals(jsobject.getString("allowance"))?null:Double.parseDouble(jsobject.getString("allowance"));
			Double other = "".equals(jsobject.getString("other"))?null:Double.parseDouble(jsobject.getString("other"));
			Double stotal = "".equals(jsobject.getString("stotal"))?null:Double.parseDouble(jsobject.getString("stotal"));
			Double tax = "".equals(jsobject.getString("tax"))?null:Double.parseDouble(jsobject.getString("tax"));
			Double dinsurance = "".equals(jsobject.getString("dinsurance"))?null:Double.parseDouble(jsobject.getString("dinsurance"));
			Double dsick = "".equals(jsobject.getString("dsick"))?null:Double.parseDouble(jsobject.getString("dsick"));
			Double dleft = "".equals(jsobject.getString("dleft"))?null:Double.parseDouble(jsobject.getString("dleft"));
			Double dtotal = "".equals(jsobject.getString("dtotal"))?null:Double.parseDouble(jsobject.getString("dtotal"));
			Double atotal = "".equals(jsobject.getString("atotal"))?null:Double.parseDouble(jsobject.getString("atotal"));
			String remarked = jsobject.getString("remarked");
			
			sql = "update ic_wage set DATE='" + date + "',GUIDEID='" + guideID
					+ "', GUIDENAME='" + guideName + "',DEPT=" + dept
					+ ",BANKNO='" + bankno + "',BASIC=" + basic + ",POST="
					+ post + ",WORKAGE=" + workage + ",EFFICIENT=" + efficient
					+ ",TRAFFIC=" + traffic + ",LUNCH=" + lunch + ",CAR=" + car
					+ ",INSURANCE=" + insurance + ",ALLOWANCE=" + allowance
					+ ",OTHER=" + other + ",STOTAL=" + stotal + ",TAX=" + tax
					+ ",DINSURANCE=" + dinsurance + ",DSICK=" + dsick
					+ ",DLEFT=" + dleft + ",DTOTAL=" + dtotal + ",ATOTAL="
					+ atotal + ",REMARKED='" + remarked + "' where ID=" + id;
			
			sqlList.add(sql);
		}
		
		try {
			boolean flag = dao.execCommand(ConstValue.DBNAME, sqlList);
			
			if (flag) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
	}
	
	/**
	 * 初始化薪资
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String initializeSalary() {
		// 当前日期
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String nowDate = df.format(new Date());
		
 		// 初始化薪资是否成功
 		boolean flag = true;
 		
 		// 是否有记录
 		String num = "";
 	
		DAO dao = new DAO();
		
		try {
			// 查询是否有当前月份的薪资记录
	 		String hasCurMonthRecSql = "select count(ID) from ic_wage where left(DATE,7)='" + nowDate.substring(0, 7) + "'";
			num = dao.getDataValue(ConstValue.DBNAME, hasCurMonthRecSql);
			
			// 新增薪资sql
	 		String insertSalarySql = "";
	 		
			// 如果没有当月薪资
			if (Integer.parseInt(num) == 0) {
				// 获取员工信息
				String queryEmpSql = "select WORKID, ENAME, DEPT from HR_EMPLOYEE where LEFTJOB=41";
				Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, queryEmpSql);

				// sql语句集合
				ArrayList<String> sqlList = new ArrayList<String>();

				if (v != null && v.size() > 0) {
					// 取得上月月份
					Calendar cal = Calendar.getInstance();
					cal.add(Calendar.MONTH, -1);
					String preDate = df.format(cal.getTime());
					
					// 员工工号,姓名,部门
					String workID = "";
					String eName = "";
					String dept = "";
					
					for (int i = 0; i < v.size(); i++) {
						workID = v.get(i).get("WORKID");
						eName = v.get(i).get("ENAME");
						dept = v.get(i).get("DEPT");
						
						// 查询此员工上个月是否有薪资记录
						String hasPreMonthRecSql = "select count(ID) from ic_wage where left(DATE,7)='" + preDate.substring(0, 7) + "' and GUIDEID='" + workID + "'";
						num = dao.getDataValue(ConstValue.DBNAME, hasPreMonthRecSql);
						
						if (Integer.parseInt(num) > 0) {
							insertSalarySql = "insert into ic_wage (DATE,GUIDEID,GUIDENAME,DEPT,BANKNO,BASIC,POST,WORKAGE,"
									+ "EFFICIENT,TRAFFIC,LUNCH,CAR,INSURANCE,ALLOWANCE,OTHER,STOTAL,TAX,DINSURANCE,DSICK,DLEFT,"
									+ "DTOTAL,ATOTAL) select DATE_ADD(DATE,INTERVAL 1 MONTH),GUIDEID,GUIDENAME,DEPT,BANKNO,BASIC,"
									+ "POST,WORKAGE,EFFICIENT,TRAFFIC,LUNCH,CAR,INSURANCE,ALLOWANCE,OTHER,STOTAL,TAX,DINSURANCE,DSICK,"
									+ "DLEFT,DTOTAL,ATOTAL from ic_wage where left(DATE,7)='" + preDate.substring(0, 7) + "' and GUIDEID='"
									+ workID + "' and GUIDENAME='" + eName + "'";
						} else  {
							insertSalarySql = "insert into ic_wage(ID,DATE,GUIDEID,GUIDENAME,DEPT) "
								+ "values(default,'" + nowDate + "','" + workID + "','" + eName + "','" + dept + "')";
						}
					
						sqlList.add(insertSalarySql);
					}

					flag = dao.execCommand(ConstValue.DBNAME, sqlList);
				}
				
				// 取出默认参数
				Vector<Hashtable<String, String>> salaryDefaultParms = this.getDefaultSalaryParm();
				
				String traffic = null;
				String dinsurance = null;
				
				if (salaryDefaultParms!=null && salaryDefaultParms.size()>0) {
					for (int j=0; j<salaryDefaultParms.size(); j++) {
						if ("235".equals(salaryDefaultParms.get(j).get("CL_CODE"))) {
							traffic = salaryDefaultParms.get(j).get("PARM");
						} else if ("236".equals(salaryDefaultParms.get(j).get("CL_CODE"))) {
							dinsurance = salaryDefaultParms.get(j).get("PARM");
						}
					}
				}
				
				// 更新默认薪资sql
		 		String updateSalarySql = "";
		 		
		 		updateSalarySql = "update ic_wage set TRAFFIC='" + traffic
						+ "', DINSURANCE='" + dinsurance
						+ "' where left(DATE,7)='" + nowDate.substring(0, 7)
						+ "'";
		 		
		 		int result = dao.execCommand(ConstValue.DBNAME, updateSalarySql);
		 		
		 		flag = flag && (result>0);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "初始化薪资失败";
		}
		
		if (flag) {
			return "success";
		} else {
			return "初始化薪资失败";
		}
	}
	
	/**
	 * 获取默认薪资参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Vector<Hashtable<String, String>> getDefaultSalaryParm() {
		String sql = "select CL_CODE, PARM from cm_commlist where PL_CODE=53";
		
		DAO dao = new DAO();
		
		Vector<Hashtable<String, String>> v = null;
		
		try {
			v = dao.getDataSet(ConstValue.DBNAME, sql);
		} catch (ServletException e) {
			e.printStackTrace();
		}
		
		return v;
	}
}
